DECLARE @timeTolerance FLOAT = (SELECT CAST(Value AS FLOAT) FROM Setting WHERE Name = 'DataAnalysis.TimeTolerance')
DECLARE @lineID INT
DECLARE @startTime DATETIME2
DECLARE @endTime DATETIME2

SELECT
    @lineID = AssetID,
    @startTime = dbo.AdjustDateTime2(StartTime, -@timeTolerance),
    @endTime = dbo.AdjustDateTime2(EndTime, @timeTolerance)
FROM Event
WHERE ID = {0}

SELECT *
INTO #lineEvent
FROM Event
WHERE
    Event.AssetID = @lineID AND
    Event.EndTime >= @startTime AND
    Event.StartTime <= @endTime

SELECT
    ROW_NUMBER() OVER(PARTITION BY Event.MeterID ORDER BY FaultSummary.Inception) AS FaultNumber,
    FaultSummary.ID AS FaultSummaryID,
    Meter.AssetKey AS MeterKey,
    Meter.Name AS MeterName,
    Location.LocationKey as StationKey,
    Location.Name AS StationName,
	Location.Description AS StationDescription,
    Asset.AssetKey AS LineKey,
    Asset.AssetName AS LineName,
    FaultSummary.FaultType,
    FaultSummary.Inception,
    DATEADD(MINUTE, -Event.TimeZoneOffset, FaultSummary.Inception) UTCInception,
    FaultSummary.DurationCycles,
    FaultSummary.DurationSeconds * 1000.0 AS DurationMilliseconds,
    CASE WHEN FaultSummary.PrefaultCurrent <> -1E308 THEN FORMAT(FaultSummary.PrefaultCurrent, '0.##########') ELSE 'NaN' END AS PrefaultCurrent,
    CASE WHEN FaultSummary.PostfaultCurrent <> -1E308 THEN FORMAT(FaultSummary.PostfaultCurrent, '0.##########') ELSE 'NaN' END AS PostfaultCurrent,
    FaultSummary.ReactanceRatio,
    FaultSummary.CurrentMagnitude AS FaultCurrent,
    FaultSummary.Algorithm,
    FaultSummary.Distance AS SingleEndedDistance,
    DoubleEndedFaultSummary.Distance AS DoubleEndedDistance,
    DoubleEndedFaultSummary.Angle AS DoubleEndedAngle,
    FaultCauseMetrics.TreeFaultResistance,
    FaultCauseMetrics.LightningMilliseconds,
    FaultCauseMetrics.InceptionDistanceFromPeak,
    FaultCauseMetrics.PrefaultThirdHarmonic,
    FaultCauseMetrics.GroundCurrentRatio,
    FaultCauseMetrics.LowPrefaultCurrentRatio,
    RIGHT(DataFile.FilePath, CHARINDEX(CHAR(92), REVERSE(DataFile.FilePath)) - 1) AS FileName,
    FaultSummary.EventID,
    Event.StartTime AS EventStartTime,
    Event.EndTime AS EventEndTime
INTO #summaryData
    CASE
        WHEN PATINDEX('%[^0]%', Asset.AssetKey) <> 0
        THEN SUBSTRING(Asset.AssetKey, PATINDEX('%[^0]%', Asset.AssetKey), LEN(Asset.AssetKey))
        ELSE '0'
    END LineKey,
    FORMAT(FaultSummary.Distance, '0.##########') Distance,
    DATEADD(MINUTE, -Event.TimeZoneOffset, FaultSummary.Inception) EventTime,
    NULL EndLatitude,
    NULL EndLongitude
FROM
    #lineEvent Event JOIN
	EventType ON
        Event.EventTypeID = EventType.ID AND
        EventType.Name = 'Fault' JOIN
    FaultSummary ON
        FaultSummary.EventID = Event.ID AND
        FaultSummary.IsSelectedAlgorithm <> 0 LEFT OUTER JOIN
    FaultCauseMetrics ON
        FaultSummary.EventID = FaultCauseMetrics.EventID AND
        FaultSummary.FaultNumber = FaultCauseMetrics.FaultNumber JOIN
    DataFile ON DataFile.FileGroupID = Event.FileGroupID JOIN
	Meter ON Event.MeterID = Meter.ID JOIN
    Location ON Meter.LocationID = Location.ID JOIN
	
    Asset ON Event.AssetID = Asset.ID LEFT OUTER JOIN
	DoubleEndedFaultDistance ON DoubleEndedFaultDistance.LocalFaultSummaryID = FaultSummary.ID LEFT OUTER JOIN
    DoubleEndedFaultSummary ON DoubleEndedFaultSummary.ID = DoubleEndedFaultDistance.ID
WHERE
    DataFile.FilePath LIKE '%.DAT' OR
    DataFile.FilePath LIKE '%.D00' OR
    DataFile.FilePath LIKE '%.PQD' OR
    DataFile.FilePath LIKE '%.RCD' OR
    DataFile.FilePath LIKE '%.RCL' OR
    DataFile.FilePath LIKE '%.SEL' OR
    DataFile.FilePath LIKE '%.EVE' OR
    DataFile.FilePath LIKE '%.CEV'

DECLARE @url VARCHAR(MAX) = (SELECT Value FROM DashSettings WHERE Name = 'System.URL')

SELECT
    (
        SELECT ID AS [@id]
        FROM #lineEvent
        FOR XML PATH('Event'), TYPE
    ) AS [Events],
    (
        SELECT
            FaultNumber AS [@num],
            (
                SELECT
                    MeterKey,
                    MeterName,
                    StationKey,
                    StationName,
					StationDescription,
                    LineKey,
                    LineName,
                    FaultType,
                    Inception,
                    UTCInception,
                    DurationCycles,
                    DurationMilliseconds,
                    PrefaultCurrent,
                    PostfaultCurrent,
                    ReactanceRatio,
                    FaultCurrent,
                    Algorithm,
                    FORMAT(SingleEndedDistance, '0.##########') AS SingleEndedDistance,
                    FORMAT(DoubleEndedDistance, '0.##########') AS DoubleEndedDistance,
                    DoubleEndedAngle,
                    FORMAT(TreeFaultResistance, '0.##########') AS TreeFaultResistance,
                    FORMAT(LightningMilliseconds, '0.##########') AS LightningMilliseconds,
                    FORMAT(InceptionDistanceFromPeak, '0.##########') AS InceptionDistanceFromPeak,
                    FORMAT(PrefaultThirdHarmonic, '0.##########') AS PrefaultThirdHarmonic,
                    FORMAT(GroundCurrentRatio, '0.##########') AS GroundCurrentRatio,
                    FORMAT(LowPrefaultCurrentRatio, '0.##########') AS LowPrefaultCurrentRatio,
                    EventStartTime,
                    EventEndTime,
                    FileName,
                    EventID,
                    FaultSummaryID AS FaultID
                FROM #summaryData
                WHERE FaultNumber = Fault.FaultNumber
                FOR XML PATH('SummaryData'), TYPE
            )
        FROM
        (
            SELECT DISTINCT FaultNumber
            FROM #summaryData
        ) Fault
        FOR XML PATH('Fault'), TYPE
    ) AS [Faults],
    Line.AssetName AS [Line/Name],
    Line.AssetKey AS [Line/AssetKey],
    FORMAT(Line.Length, '0.##########') AS [Line/Length],
    FORMAT(SQRT(Line.R1 * Line.R1 + Line.X1 * Line.X1), '0.##########') AS [Line/Z1],
    CASE Line.R1 WHEN 0 THEN '0' ELSE FORMAT(ATN2(Line.X1, Line.R1) * 180 / PI(), '0.##########') END AS [Line/A1],
    FORMAT(Line.R1, '0.##########') AS [Line/R1],
    FORMAT(Line.X1, '0.##########') AS [Line/X1],
    FORMAT(SQRT(Line.R0 * Line.R0 + Line.X0 * Line.X0), '0.##########') AS [Line/Z0],
    CASE Line.R0 WHEN 0 THEN '0' ELSE FORMAT(ATN2(Line.X0, Line.R0) * 180 / PI(), '0.##########') END AS [Line/A0],
    FORMAT(Line.R0, '0.##########') AS [Line/R0],
    FORMAT(Line.X0, '0.##########') AS [Line/X0],
    FORMAT(SQRT(POWER((2.0 * Line.R1 + Line.R0) / 3.0, 2) + POWER((2.0 * Line.X1 + Line.X0) / 3.0, 2)), '0.##########') AS [Line/ZS],
    CASE 2.0 * Line.R1 + Line.R0 WHEN 0 THEN '0' ELSE FORMAT(ATN2((2.0 * Line.X1 + Line.X0) / 3.0, (2.0 * Line.R1 + Line.R0) / 3.0) * 180 / PI(), '0.##########') END AS [Line/AS],
    FORMAT((2.0 * Line.R1 + Line.R0) / 3.0, '0.##########') AS [Line/RS],
    FORMAT((2.0 * Line.X1 + Line.X0) / 3.0, '0.##########') AS [Line/XS],
    @url AS [PQDashboard]
FROM
    Event JOIN
    LineView Line ON Event.AssetID = Line.ID
WHERE Event.ID = {0}
FOR XML PATH('EventDetail'), TYPE